<?php

namespace org\copperforest\oak\sql;

class SQLParser
{

    private $engine;
    private $version;
    private $reserveds;

    function __construct( $engine, $version = null )
    {
        $this->engine = strtolower( $engine );
        $this->version = strtolower( $version );

        $this->reserveds = array( );

        switch ( $this->engine ) {
            case 'mysql':


                if ( (( float ) $this->version) >= 4 || $this->version == null )
                        $this->reserveds = array_merge( $this->reserveds,
                            array(
                        "ADD", "ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC", "BEFORE",
                        "BETWEEN", "BIGINT", "BINARY", "BLOB", "BOTH", "BY", "CASCADE",
                        "CASE", "CHANGE", "CHAR", "CHARACTER", "CHECK", "COLLATE",
                        "COLUMN", "CONSTRAINT", "CONVERT", "CREATE", "CROSS", "CURRENT_DATE",
                        "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER",
                        "DATABASE", "DATABASES", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE",
                        "DAY_SECOND", "DEC", "DECIMAL", "DEFAULT", "DELAYED", "DELETE",
                        "DESC", "DESCRIBE", "DISTINCT", "DISTINCTROW", "DIV", "DOUBLE",
                        "DROP", "DUAL", "ELSE", "ENCLOSED", "ESCAPED", "EXISTS",
                        "EXPLAIN",
                        "FALSE", "FLOAT", "FLOAT4", "FLOAT8", "FOR", "FORCE", "FOREIGN",
                        "FROM", "FULLTEXT", "GRANT", "GROUP", "HAVING", "HIGH_PRIORITY",
                        "HOUR_MICROSECOND", "HOUR_MINUTE", "HOUR_SECOND", "IF", "IGNORE",
                        "IN", "INDEX", "INFILE", "INNER", "INSERT", "INT", "INT1",
                        "INT2", "INT3", "INT4", "INT8", "INTEGER", "INTERVAL", "INTO",
                        "IS",
                        "JOIN", "KEY", "KEYS", "KILL", "LEADING", "LEFT", "LIKE",
                        "LIMIT", "LINES", "LOAD", "LOCALTIME", "LOCALTIMESTAMP",
                        "LOCK", "LONG", "LONGBLOB", "LONGTEXT", "LOW_PRIORITY", "MATCH",
                        "MEDIUMBLOB", "MEDIUMINT", "MEDIUMTEXT", "MIDDLEINT", "MINUTE_MICROSECOND",
                        "MINUTE_SECOND", "MOD", "NATURAL", "NOT", "NO_WRITE_TO_BINLOG",
                        "NULL", "NUMERIC",
                        "ON", "OPTIMIZE", "OPTION", "OPTIONALLY", "OR", "ORDER",
                        "OUTER", "OUTFILE", "PRECISION", "PRIMARY", "PROCEDURE",
                        "PURGE", "READ", "REAL", "REFERENCES", "REGEXP", "RENAME",
                        "REPLACE", "REQUIRE", "RESTRICT", "REVOKE", "RIGHT", "RLIKE",
                        "SECOND_MICROSECOND", "SELECT", "SEPARATOR", "SET", "SHOW",
                        "SMALLINT", "SONAME", "SPATIAL", "SQL_BIG_RESULT", "SQL_CALC_FOUND_ROWS",
                        "SQL_SMALL_RESULT", "SSL", "STARTING", "STRAIGHT_JOIN", "TABLE",
                        "TERMINATED", "THEN", "TINYBLOB", "TINYINT", "TINYTEXT",
                        "TO", "TRAILING", "TRUE",
                        "UNION", "UNIQUE", "UNLOCK", "UNSIGNED", "UPDATE", "USAGE",
                        "USE", "USING", "UTC_DATE", "UTC_TIME", "UTC_TIMESTAMP",
                        "VALUES", "VARBINARY", "VARCHAR", "VARCHARACTER", "VARYING",
                        "WHEN", "WHERE", "WITH", "WRITE", "XOR", "YEAR_MONTH", "ZEROFILL" ) );

                if ( (( float ) $this->version) >= 5.0 || $this->version == null )
                        $this->reserveds = array_merge( $this->reserveds,
                            array( "ASENSITIVE", "CALL", "CONDITION", "CONNECTION",
                        "CONTINUE", "CURSOR", "DECLARE", "DETERMINISTIC", "EACH",
                        "ELSEIF", "EXIT", "FETCH", "GOTO", "INOUT", "INSENSITIVE",
                        "ITERATE", "LABEL", "LEAVE", "LOOP", "MODIFIES", "OUT", "READS",
                        "RELEASE", "REPEAT", "RETURN", "SCHEMA", "SCHEMAS", "SENSITIVE",
                        "SPECIFIC", "SQL", "SQLEXCEPTION", "SQLSTATE", "SQLWARNING",
                        "TRIGGER", "UNDO", "UPGRADE", "WHILE" ) );

                if ( (( float ) $this->version) >= 5.2 || $this->version == null )
                        $this->reserveds = array_merge( $this->reserveds,
                            array( "ACCESSIBLE", "LINEAR", "MASTER_SSL_VERIFY_SERVER_CERT",
                        "RANGE", "READ_WRITE", "RESIGNAL", "SIGNAL" ) );

                if ( (( float ) $this->version) >= 5.5 || $this->version == null )
                        $this->reserveds = array_merge( $this->reserveds,
                            array( "GENERAL", "IGNORE_SERVER_IDS", "MASTER_HEARTBEAT_PERIOD",
                        "MAXVALUE", "SLOW" ) );

                break;

            case 'mssql':

                $odbc = array( "ABSOLUTE", "ACTION", "ADA", "ADD", "ALL", "ALLOCATE",
                    "ALTER", "AND", "ANY", "ARE", "AS", "ASC", "ASSERTION", "AT",
                    "AUTHORIZATION", "AVG", "BEGIN", "BETWEEN", "BIT", "BIT_LENGTH",
                    "BOTH", "BY", "CASCADE", "CASCADED", "CASE", "CAST", "CATALOG",
                    "CHAR", "CHAR_LENGTH", "CHARACTER", "CHARACTER_LENGTH", "CHECK",
                    "CLOSE", "COALESCE", "COLLATE", "COLLATION", "COLUMN", "COMMIT",
                    "CONNECT", "CONNECTION", "CONSTRAINT", "CONSTRAINTS", "CONTINUE",
                    "CONVERT", "CORRESPONDING", "COUNT", "CREATE", "CROSS", "CURRENT",
                    "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER",
                    "CURSOR",
                    "DATE", "DAY", "DEALLOCATE", "DEC", "DECIMAL", "DECLARE", "DEFAULT",
                    "DEFERRABLE", "DEFERRED", "DELETE", "DESC", "DESCRIBE", "DESCRIPTOR",
                    "DIAGNOSTICS", "DISCONNECT", "DISTINCT", "DOMAIN", "DOUBLE",
                    "DROP", "ELSE", "END", "END-EXEC", "ESCAPE", "EXCEPT", "EXCEPTION",
                    "EXEC", "EXECUTE", "EXISTS", "EXTERNAL", "EXTRACT", "FALSE",
                    "FETCH", "FIRST", "FLOAT", "FOR", "FOREIGN", "FORTRAN", "FOUND",
                    "FROM", "FULL",
                    "GET", "GLOBAL", "GO", "GOTO", "GRANT", "GROUP", "HAVING", "HOUR",
                    "IDENTITY", "IMMEDIATE", "IN", "INCLUDE", "INDEX", "INDICATOR",
                    "INITIALLY", "INNER", "INPUT", "INSENSITIVE", "INSERT", "INT",
                    "INTEGER", "INTERSECT", "INTERVAL", "INTO", "IS", "ISOLATION",
                    "JOIN", "KEY", "LANGUAGE", "LAST", "LEADING", "LEFT", "LEVEL",
                    "LIKE", "LOCAL", "LOWER", "MATCH", "MAX", "MIN", "MINUTE", "MODULE",
                    "MONTH", "NAMES", "NATIONAL", "NATURAL", "NCHAR", "NEXT", "NO",
                    "NONE", "NOT", "NULL", "NULLIF", "NUMERIC",
                    "OCTET_LENGTH", "OF", "ON", "ONLY", "OPEN", "OPTION", "OR", "ORDER",
                    "OUTER", "OUTPUT", "OVERLAPS", "PAD", "PARTIAL", "PASCAL", "POSITION",
                    "PRECISION", "PREPARE", "PRESERVE", "PRIMARY", "PRIOR", "PRIVILEGES",
                    "PROCEDURE", "PUBLIC", "READ", "REAL", "REFERENCES", "RELATIVE",
                    "RESTRICT", "REVOKE", "RIGHT", "ROLLBACK", "ROWS", "SCHEMA",
                    "SCROLL", "SECOND", "SECTION", "SELECT", "SESSION", "SESSION_USER",
                    "SET", "SIZE", "SMALLINT", "SOME", "SPACE", "SQL", "SQLCA", "SQLCODE",
                    "SQLERROR", "SQLSTATE", "SQLWARNING", "SUBSTRING", "SUM", "SYSTEM_USER",
                    "TABLE", "TEMPORARY", "THEN", "TIME", "TIMESTAMP", "TIMEZONE_HOUR",
                    "TIMEZONE_MINUTE", "TO", "TRAILING", "TRANSACTION", "TRANSLATE",
                    "TRANSLATION", "TRIM", "TRUE", "UNION", "UNIQUE", "UNKNOWN",
                    "UPDATE", "UPPER", "USAGE", "USER", "USING", "VALUE", "VALUES",
                    "VARCHAR", "VARYING", "VIEW", "WHEN", "WHENEVER", "WHERE", "WITH",
                    "WORK", "WRITE", "YEAR", "ZONE" );


                $mssql2005 = array( "BACKUP", "BREAK", "BROWSE", "BULK", "CHECKPOINT",
                    "CLUSTERED", "COMPUTE", "CONTAINSTABLE",
                    "DATABASE", "DBCC", "DENY", "DISK", "DISTRIBUTED", "DUMP", "ERRLVL",
                    "EXIT", "FILE", "FILLFACTOR", "FREETEXT", "FREETEXTTABLE", "FUNCTION",
                    "HOLDLOCK",
                    "IDENTITY_INSERT", "IDENTITYCOL", "IF", "LINENO", "LOAD", "NOCHECK",
                    "NONCLUSTERED",
                    "OFF", "OFFSETS", "OPENDATASOURCE", "OPENQUERY", "OPENROWSET",
                    "OPENXML", "OVER", "PERCENT", "PIVOT", "PLAN", "PRINT", "PROC",
                    "RAISERROR", "READTEXT",
                    "RECONFIGURE", "REPLICATION", "RESTORE", "RETURN", "REVERT",
                    "ROWCOUNT", "ROWGUIDCOL", "RULE", "SAVE", "SECURITYAUDIT", "SETUSER",
                    "SHUTDOWN", "STATISTICS", "SYSTEM_USER",
                    "TABLESAMPLE", "TEXTSIZE", "TOP", "TRAN", "TRIGGER", "TRUNCATE",
                    "TSEQUAL", "UNPIVOT", "UPDATETEXT", "USE", "VALUES", "WAITFOR",
                    "WHILE", "WRITETEXT" );

                $mssql2008 = array( "MERGE" );

                $mssql2012 = array( "SEMANTICKEYPHRASETABLE", "SEMANTICSIMILARITYDETAILSTABLE",
                    "SEMANTICSIMILARITYTABLE", "TRY_CONVERT", "TRY_CONVERT" );

                /*
                  $future=array("ADMIN","AFTER","AGGREGATE","ALIAS","ALLOCATE","ARE","ARRAY","ASENSITIVE","ASSERTION","ASYMMETRIC","AT","ATOMIC","BEFORE","BINARY","BIT","BLOB","BOOLEAN","BOTH","BREADTH","CALL","CALLED","CARDINALITY","CASCADED","CAST","CATALOG","CHAR","CHARACTER","CLASS","CLOB","COLLATION","COLLECT","COMPLETION","CONDITION","CONNECT","CONNECTION","CONSTRAINTS","CONSTRUCTOR","CORR","CORRESPONDING","COVAR_POP","COVAR_SAMP","CUBE","CUME_DIST","CURRENT_CATALOG","CURRENT_DEFAULT_TRANSFORM_GROUP","CURRENT_PATH","CURRENT_ROLE","CURRENT_SCHEMA","CURRENT_TRANSFORM_GROUP_FOR_TYPE","CYCLE",

                  "DATA","DATE","DAY","DEC","DECIMAL","DEFERRABLE","DEFERRED","DEPTH","DEREF","DESCRIBE","DESCRIPTOR","DESTROY","DESTRUCTOR","DETERMINISTIC","DICTIONARY","DIAGNOSTICS","DISCONNECT","DOMAIN","DYNAMIC","EACH","ELEMENT","END-EXEC","EQUALS","EVERY","EXCEPTION","FALSE","FILTER","FIRST","FLOAT","FOUND","FREE","FULLTEXTTABLE","FUSION","GENERAL","GET","GLOBAL","GO","GROUPING","HOLD","HOST","HOUR","IGNORE","IMMEDIATE","INDICATOR","INITIALIZE","INITIALLY","INOUT","INPUT","INT","INTEGER","INTERSECTION","INTERVAL","ISOLATION","ITERATE",
                  "LANGUAGE","LARGE","LAST","LATERAL","LEADING","LESS","LEVEL","LIKE_REGEX","LIMIT","LN","LOCAL","LOCALTIME","LOCALTIMESTAMP","LOCATOR","MAP","MATCH","MEMBER","METHOD","MINUTE","MOD","MODIFIES","MODIFY","MODULE","MONTH","MULTISET","NAMES","NATURAL","NCHAR","NCLOB","NEW","NEXT","NO","NONE","NORMALIZE","NUMERIC","OBJECT","OCCURRENCES_REGEX","OLD","ONLY","OPERATION","ORDINALITY","OUT","OVERLAY","OUTPUT","PAD","PARAMETER","PARAMETERS","PARTIAL","PARTITION","PATH","POSTFIX","PREFIX","PREORDER","PREPARE","PERCENT_RANK","PERCENTILE_CONT","PERCENTILE_DISC","POSITION_REGEX","PRESERVE","PRIOR","PRIVILEGES",
                  "RANGE","READS","REAL","RECURSIVE","REF","REFERENCING","REGR_AVGX","REGR_AVGY","REGR_COUNT","REGR_INTERCEPT","REGR_R2","REGR_SLOPE","REGR_SXX","REGR_SXY","REGR_SYY","RELATIVE","RELEASE","RESULT","RETURNS","ROLE","ROLLUP","ROUTINE","ROW","ROWS","SAVEPOINT","SCROLL","SCOPE","SEARCH","SECOND","SECTION","SENSITIVE","SEQUENCE","SESSION","SETS","SIMILAR","SIZE","SMALLINT","SPACE","SPECIFIC","SPECIFICTYPE","SQL","SQLEXCEPTION","SQLSTATE","SQLWARNING","START","STATE","STATEMENT","STATIC","STDDEV_POP","STDDEV_SAMP","STRUCTURE","SUBMULTISET","SUBSTRING_REGEX","SYMMETRIC","SYSTEM",
                  "TEMPORARY","TERMINATE","THAN","TIME","TIMESTAMP","TIMEZONE_HOUR","TIMEZONE_MINUTE","TRAILING","TRANSLATE_REGEX","TRANSLATION","TREAT","TRUE","UESCAPE","UNDER","UNKNOWN","UNNEST","USAGE","USING","VALUE","VAR_POP","VAR_SAMP","VARCHAR","VARIABLE","WHENEVER","WIDTH_BUCKET","WITHOUT","WINDOW","WITHIN","WORK","WRITE","XMLAGG","XMLATTRIBUTES","XMLBINARY","XMLCAST","XMLCOMMENT","XMLCONCAT","XMLDOCUMENT","XMLELEMENT","XMLEXISTS","XMLFOREST","XMLITERATE","XMLNAMESPACES","XMLPARSE","XMLPI","XMLQUERY","XMLSERIALIZE","XMLTABLE","XMLTEXT","XMLVALIDATE","YEAR","ZONE");
                 */

                if ( $this->version == '2005' )
                        $this->reserveds = array_merge( $odbc, $mssql2005 );

                elseif ( $this->version == '2008' || $this->version == '2008r2' )
                        $this->reserveds = array_merge( $odbc, $mssql2005,
                            $mssql2008 );

                else if ( $this->version == '2012' || $this->version == null )
                        $this->reserveds = array_merge( $odbc, $mssql2005,
                            $mssql2008, $mssql2012 );


                break;
        }
    }

    function isNumeric( $type )
    {
        switch ( $this->engine ) {
            case 'mysql':
                return in_array( strtoupper( $type ),
                        array( 'INTEGER', 'SMALLINT', 'DECIMAL', 'NUMERIC', 'FLOAT',
                    'REAL', 'DOUBLE PRECISION', 'INT', 'DEC', 'TINYINT', 'MEDIUMINT',
                    'BIGINT', 'BIT' ) );
            case 'mssql':
                return in_array( strtoupper( $type ),
                        array( 'BIGINT', 'NUMERIC', 'BIT', 'SMALLINT', 'DECIMAL',
                    'SMALLMONEY', 'MONEY', 'INT', 'TINYINT', 'FLOAT', 'REAL' ) );
        }
    }

    function getIntegerType( $min, $max )
    {
        switch ( $this->engine ) {
            case 'mysql':
                if ( $min == 0 && $max == 1 ) return 'tinyint(1)';

                else if ( $max <= 255 && $min >= 0 ) return 'tinyint UNSIGNED';

                else if ( $max <= 127 && $min >= -128 ) return 'tinyint';

                else if ( $max <= 65535 && $min >= 0 )
                        return 'smallint UNSIGNED';

                else if ( $max <= 32767 && $min >= -32768 ) return 'smallint';

                else if ( $max <= 16777215 && $min >= 0 )
                        return 'mediumint UNSIGNED';

                else if ( $max <= 8388607 && $min >= -8388608 )
                        return 'mediumint';

                else if ( $max <= 2147483647 && $min >= -2147483648 )
                        return 'int';

                else if ( $max <= 18446744073709551615 && $min >= 0 )
                        return 'bigint UNSIGNED';

                else if ( $max <= 9223372036854775807 && $min >= -9223372036854775808 )
                        return 'bigint';

                return false;
                break;

            case 'mssql':

                if ( $min == 0 && $max == 1 ) return 'bit';

                else if ( $max <= 255 && $min >= 0 ) return 'tinyint';

                else if ( $max <= 32767 && $min >= 32768 ) return 'smallint';

                else if ( $max <= 2147483647 && $min >= 2147483648 )
                        return 'int';

                else if ( $max <= 9223372036854775807 && $min >= 9223372036854775808 )
                        return 'bigint';

                return false;
                break;
        }
    }

    function geDecimalType( $precision, $decimals )
    {
        return 'decimal(' . $precision . ',' . $decimals . ')';
    }

    function getFloatType( $decimals )
    {
        if ( $decimals <= 7 ) return 'float(24)';
        else return 'float(53)';
    }

    function autoincrement()
    {
        switch ( $this->engine ) {
            case 'mysql':
                return 'AUTO_INCREMENT';
            case 'mssql':
                return 'IDENTITY(1,1)';
        }
    }

    function isReserved( $string )
    {
        return in_array( $string, $this->reserveds );
    }

    function exploreSELECT( $sql )
    {
        $sql = str_replace( array( "\r", "\n" ), array( ' ', ' ' ), $sql );
        $sql = self::stripDoubleBlanks( $sql );


        if ( self::sqlSearch( $sql, 'UNION' ) !== false ) {
            $chunks = self::sqlSplit( $sql, 'UNION' );

            $structs = array( );
            foreach ( $chunks as $sql )
                    $structs[] = $this->exploreSimpleSELECT( $sql );

            return $structs;
        }
        else return $this->exploreSimpleSELECT( $sql );
    }

    private function exploreSimpleSELECT( $sql )
    { //@TODO NO ESTA PREPARADO TODAVÍA PARA SENTENCIAS UNION
        $sql = str_replace( array( "\r", "\n" ), array( ' ', ' ' ), $sql );
        $sql = self::stripDoubleBlanks( $sql );



        if ( self::sqlSearch( $sql, 'SELECT' ) !== 0 ) return false;

        $struct = array(
            'reserved' => '',
            'fields' => array( ),
            'from' => '',
            'tables' => array( ),
            'where' => '',
            'group by' => '',
            'order by' => '',
            'limit' => ''
        );

        //antes de nada debería dividir por el UNION y tratarlas como dos consultas

        $positions = array( );

        $positions[ 0 ] = self::sqlSearch( $sql, 'FROM' );
        if ( $positions[ 0 ] == false ) return false;

        $select = substr( $sql, 7, $positions[ 0 ] - 7 - 1 ); //Quito espacios de inicio y fin


        $positions[ 1 ] = self::sqlSearch( $sql, 'WHERE' );
        $positions[ 2 ] = self::sqlSearch( $sql, 'GROUP BY' );
        $positions[ 3 ] = self::sqlSearch( $sql, 'ORDER BY' );
        $positions[ 4 ] = self::sqlSearch( $sql, 'LIMIT' );

        $labels = array( 'from', 'where', 'group by', 'order by', 'limit' );


        for ( $pointer = 0; $pointer < 5; $pointer++ ) {

            if ( $positions[ $pointer ] !== false ) {
                $currentpos = $positions[ $pointer ] + strlen( $labels[ $pointer ] ) + 1;

                $i = $pointer + 1;
                while ( $i < 5 && $positions[ $i ] === false )
                    $i++;

                if ( $i < 5 )
                        $struct[ $labels[ $pointer ] ] = substr( $sql,
                            $currentpos, $positions[ $i ] - $currentpos - 1 );
                else {
                    $struct[ $labels[ $pointer ] ] = rtrim( substr( $sql,
                                    $currentpos ) );
                    break;
                }
            }
        }

        unset( $positions, $labels );

        //AHORA TENGO QUE VER SI TENGO ALGUNA PALABRA RESERVADA ANTES DE LOS CAMPOS
        //tengo que buscar la primera palabra, si es distinta de alguna de las reservadas significaría que es un campo
        //extraemos las palabras reservadas del select




        $posespacio = self::sqlSearch( $select, ' ' );

        while ( $posespacio !== false ) {

            if ( !in_array( strtoupper( substr( $select, 0, $posespacio ) ),
                            $this->reserveds ) ) break;

            else {
                $struct[ 'reserved' ].=' ' . substr( $select, 0, $posespacio );
                $select = substr( $select, $posespacio + 1 );
                $posespacio = self::sqlSearch( $select, ' ' );
            }
        }

        //ahora en el SELECT tienen que estar todos los campos
        //una vez supero las reservadas las demás son campos

        $chunks = self::sqlSplit( $select, ',' );

        //ahora tengo que ver las posibles opciones que hay
        //tengo que considerar el patron con Base de datos al ppo?? puede ser, si están asi nombradas en el from para evitar campos ambiguos habría que ponerlo
        if ( $this->engine == 'mysql' ) {
            $pattern = '/^\s?(((`([^`]*(``[^`]*)*)`|[a-z_][a-z0-9_]*)\s?\.\s?)?(`([^`]*(``[^`]*)*)`|[a-z_][a-z0-9_]*)\s?\.\s?)?(`([^`]*(``[^`]*)*)`|[a-z_][a-z0-9_]*)((\sas)\s?(`([^`]*(``[^`]*)*)`|[a-z_][a-z0-9_]*))?\s*$/i';
            $pattern_as = '/(\sas)?\s(`([^`]*(``[^`]*)*)`|[a-z_][a-z0-9_]*)\s*$/i';
        } else if ( $this->engine == 'mssql' ) {
            //ojo, aqui puede haber catalogo.esquema
            //OJO NO ES OBLIGATORIO EL AS, creo que en mysql tpc
            $pattern = '/^\s?((((\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*)\s?\.\s?)?(\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*)\s?\.\s?)?(\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*)\s?\.\s?)?(\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*)((\sas)?\s(\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*))?\s*$/i';
            $pattern_as = '/(\sas)?\s(\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*)\s*$/i';
        }

        foreach ( $chunks as $c ) {

            $matches = array( );

            if ( preg_match( $pattern, $c, $matches ) ) {

                $alias = '';
                $fulldesc = '';
                $field = '';
                $table = '';
                $schema = '';
                $catalog = '';

                if ( $this->engine == 'mysql' ) {
                    //  $catalog='def';
                    //el 1 con el 9 puedeo hacer el campo entero
                    $fulldesc = $matches[ 1 ] . $matches[ 9 ];
                    //if(!empty($matches[1]))

                    if ( empty( $matches[ 4 ] ) ) $schema = $matches[ 3 ];
                    else $schema = strtr( $matches[ 4 ], array( '``' => '`' ) );

                    if ( empty( $matches[ 7 ] ) ) $table = $matches[ 6 ];
                    else $table = strtr( $matches[ 7 ], array( '``' => '`' ) );

                    if ( empty( $matches[ 10 ] ) ) $field = $matches[ 9 ];
                    else $field = strtr( $matches[ 10 ], array( '``' => '`' ) );


                    if ( !empty( $matches[ 15 ] ) )
                            $alias = strtr( $matches[ 15 ], array( '``' => '`' ) );
                    else if ( !empty( $matches[ 14 ] ) )
                            $alias = $matches[ 14 ];
                }
                else if ( $this->engine == 'mssql' ) {

                    $fulldesc = $matches[ 1 ] . $matches[ 13 ];

                    if ( empty( $matches[ 5 ] ) ) $catalog = $matches[ 4 ];
                    else
                            $catalog = strtr( $matches[ 5 ],
                                array( ']]' => ']' ) );

                    if ( empty( $matches[ 8 ] ) ) $schema = $matches[ 7 ];
                    else $schema = strtr( $matches[ 8 ], array( ']]' => ']' ) );


                    if ( empty( $matches[ 11 ] ) ) $table = $matches[ 10 ];
                    else $table = strtr( $matches[ 11 ], array( ']]' => ']' ) );

                    if ( empty( $matches[ 14 ] ) ) $field = $matches[ 13 ];
                    else $field = strtr( $matches[ 14 ], array( ']]' => ']' ) );


                    if ( !empty( $matches[ 19 ] ) )
                            $alias = strtr( $matches[ 19 ], array( ']]' => ']' ) );
                    else if ( !empty( $matches[ 18 ] ) )
                            $alias = $matches[ 18 ];
                }

                $struct[ 'fields' ][ ] = array( 'catalog' => $catalog, 'schema' => $schema,
                    'table' => $table, 'field' => $field, 'alias' => $alias, 'field_org' => $fulldesc );
            }
            else if ( preg_match( $pattern_as, $c, $matches ) ) { //si es una funcion deberá tener alias alias!!!!
                if ( $this->engine == 'mysql' ) {
                    if ( empty( $matches[ 3 ] ) ) $alias = $matches[ 2 ];
                    else $alias = strtr( $matches[ 3 ], array( '``' => '`' ) );
                }
                else if ( $this->engine == 'mssql' ) {
                    if ( empty( $matches[ 3 ] ) ) $alias = $matches[ 2 ];
                    else $alias = strtr( $matches[ 3 ], array( ']]' => ']' ) );
                }

                $struct[ 'fields' ][ ] = array( 'alias' => $alias, 'field_org' => trim( substr( $c,
                                    0, -1 * strlen( $matches[ 0 ] ) ) ) );
            }
            else $struct[ 'fields' ][ ] = array( 'field_org' => trim( $c ) );
        }


        //ahora extraigo todas las tablas del from
        $from = ',' . $struct[ 'from' ];


        //EL PROBLEMA COMO DIFERENCIO LAS PALABRAS REVESERVADAS DEL ALIAS??
        if ( $this->engine == 'mysql' )
                $pattern = '/(join\s|,\s?)((`([^`]*(``[^`]*)*)`|[a-z_][a-z0-9_]*)\s?\.\s?)?(`([^`]*(``[^`]*)*)`|[a-z_][a-z0-9_]*)((\sas)?\s(`([^`]*(``[^`]*)*)`|[a-z_][a-z0-9_]*))?/i'; //debería incorporar toda la bofia del final, USE, WITH
        else if ( $this->engine == 'mssql' )
                $pattern = '/(join\s|,\s?)(((\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*)\s?\.\s?)?(\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*)\s?\.\s?)?(\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*)((\sas)?\s(\[([^\]]*(\]\][^\]]*)*)\]|[a-z_][a-z0-9_]*))?/i'; //debería incorporar toda la bofia del final, USE, WITH

        $matches = array( );

        if ( preg_match_all( $pattern, $from, $matches ) ) {

            $num_tables = count( $matches[ 0 ] );

            for ( $i = 0; $i < $num_tables; $i++ ) {

                $table_desc = '';
                $extra = '';
                $alias = '';
                $table = '';
                $schema = '';
                $catalog = '';


                if ( $this->engine == 'mysql' ) {
                    //  $catalog='def';

                    if ( empty( $matches[ 4 ][ $i ] ) )
                            $schema = $matches[ 3 ][ $i ];
                    else
                            $schema = strtr( $matches[ 4 ][ $i ],
                                array( '``' => '`' ) );

                    if ( empty( $matches[ 7 ][ $i ] ) )
                            $table = $matches[ 6 ][ $i ];
                    else
                            $table = strtr( $matches[ 7 ][ $i ],
                                array( '``' => '`' ) );


                    if ( !empty( $matches[ 11 ] ) ) {

                        if ( !in_array( $matches[ 11 ][ $i ], $this->reserveds ) ) {

                            if ( !empty( $matches[ 12 ][ $i ] ) )
                                    $alias = strtr( $matches[ 12 ][ $i ],
                                        array( '``' => '`' ) );
                            else if ( !empty( $matches[ 11 ][ $i ] ) )
                                    $alias = $matches[ 11 ][ $i ];
                        }
                        //else
                        //    $extra=trim($matches[11][$i].$matches[13][$i]);
                    }

                    $table_desc = $matches[ 2 ][ $i ] . $matches[ 6 ][ $i ];
                }
                else if ( $this->engine == 'mssql' ) {

                    if ( empty( $matches[ 5 ][ $i ] ) )
                            $catalog = $matches[ 4 ][ $i ];
                    else
                            $catalog = strtr( $matches[ 5 ],
                                array( ']]' => ']' ) );

                    if ( empty( $matches[ 8 ][ $i ] ) )
                            $schema = $matches[ 7 ][ $i ];
                    else $schema = strtr( $matches[ 8 ], array( ']]' => ']' ) );

                    if ( empty( $matches[ 11 ][ $i ] ) )
                            $table = $matches[ 10 ][ $i ];
                    else $table = strtr( $matches[ 11 ], array( ']]' => ']' ) );


                    if ( !empty( $matches[ 15 ] ) ) {

                        if ( !in_array( $matches[ 15 ][ $i ], $this->reserveds ) ) {

                            if ( !empty( $matches[ 16 ][ $i ] ) )
                                    $alias = strtr( $matches[ 16 ][ $i ],
                                        array( '``' => '`' ) );
                            else if ( !empty( $matches[ 15 ][ $i ] ) )
                                    $alias = $matches[ 15 ][ $i ];
                        }
                        //else
                        //    $extra=trim($matches[15][$i].$matches[17][$i]);
                    }

                    $table_desc = $matches[ 2 ][ $i ] . $matches[ 10 ][ $i ];
                }

                $struct[ 'tables' ][ ] = array( 'catalog' => $catalog, 'schema' => $schema,
                    'table' => $table, 'alias' => $alias, 'extra' => $extra, 'table_org' => $table_desc );
            }
        }

        //aqui tb puedo asociar los campos a cada tabla? no, ya que casi siempre hay ambi¨guedad
        return $struct;
    }

    static function sqlSearch( $sql, $tag, $pos = 0 )
    {
        $state = 'A';
        $len = strlen( $sql );
        $string = '';
        $current = $pos;
        $tag = strtolower( $tag );
        $num_parenthesis = 0;

        while ( $current < $len ) {
            switch ( $state ) {

                case 'A':
                    if ( $sql[ $current ] == '`' ) {
                        $state = 'B';
                        $string = '';
                    } else if ( $sql[ $current ] == '\'' ) {
                        $state = 'C';
                        $string = '';
                    } else if ( $sql[ $current ] == '"' ) {
                        $state = 'D';
                        $string = '';
                    } else if ( $sql[ $current ] == '[' ) {
                        $state = 'F';
                        $string = '';
                    } else if ( $sql[ $current ] == '(' ) {
                        $state = 'E';
                        $string = '';
                        $num_parenthesis++;
                    }
                    else $string.=$sql[ $current ];

                    if ( strrpos( strtolower( $string ), $tag ) > -1 )
                            return ( $current - strlen( $tag ) + 1 );

                    break;

                case 'B':
                    if ( $sql[ $current ] == '`' ) $state = 'A';
                    break;

                case 'C':
                    if ( $sql[ $current ] == '\'' ) $state = 'A';
                    break;

                case 'D':
                    if ( $sql[ $current ] == '"' ) $state = 'A';
                    break;

                case 'F':
                    if ( $sql[ $current ] == ']' ) $state = 'A';
                    break;

                case 'E':
                    if ( $sql[ $current ] == ')' ) $num_parenthesis--;

                    else if ( $sql[ $current ] == '(' ) $num_parenthesis++;

                    if ( $num_parenthesis == 0 ) $state = 'A';

                    break;
            }

            $current++;
        }

        return false;
    }

    static function sqlSplit( $sql, $tag )
    {
        $state = 'A';
        $len = strlen( $sql );
        $tag_len = -1 * strlen( $tag );
        $current = 0;
        // $tag = strtolower($tag);
        $num_parenthesis = 0;
        $chunks = array( '' );
        $current_chunk = 0;

        while ( $current < $len ) {
            switch ( $state ) {

                case 'A':
                    if ( $sql[ $current ] == '`' ) {
                        $state = 'B';
                    } else if ( $sql[ $current ] == '\'' ) {
                        $state = 'C';
                    } else if ( $sql[ $current ] == '"' ) {
                        $state = 'D';
                    } else if ( $sql[ $current ] == '[' ) {
                        $state = 'F';
                    } else if ( $sql[ $current ] == '(' ) {
                        $state = 'E';
                        $num_parenthesis++;
                    }

                    $chunks[ $current_chunk ].=$sql[ $current ];

                    if ( substr( $chunks[ $current_chunk ], $tag_len ) == $tag ) {
                        $chunks[ $current_chunk ] = substr( $chunks[ $current_chunk ],
                                0, $tag_len );
                        $current_chunk++;
                        $chunks[ $current_chunk ] = '';
                    }



                    break;

                case 'B':
                    if ( $sql[ $current ] == '`' ) $state = 'A';

                    $chunks[ $current_chunk ].=$sql[ $current ];
                    break;

                case 'C':
                    if ( $sql[ $current ] == '\'' ) $state = 'A';

                    $chunks[ $current_chunk ].=$sql[ $current ];
                    break;

                case 'D':
                    if ( $sql[ $current ] == '"' ) $state = 'A';

                    $chunks[ $current_chunk ].=$sql[ $current ];
                    break;

                case 'F':
                    if ( $sql[ $current ] == ']' ) $state = 'A';

                    $chunks[ $current_chunk ].=$sql[ $current ];
                    break;

                case 'E':
                    if ( $sql[ $current ] == ')' ) $num_parenthesis--;

                    else if ( $sql[ $current ] == '(' ) $num_parenthesis++;

                    if ( $num_parenthesis == 0 ) $state = 'A';

                    $chunks[ $current_chunk ].=$sql[ $current ];
                    break;
            }

            $current++;
        }

        return $chunks;
    }

    static function stripDoubleBlanks( $sql )
    {
        $state = 'A';
        $len = strlen( $sql );
        $string = '';
        $current = 0;

        while ( $current < $len ) {
            switch ( $state ) {

                case 'A':
                    if ( $sql[ $current ] == '`' ) $state = 'B';

                    else if ( $sql[ $current ] == '\'' ) $state = 'C';

                    else if ( $sql[ $current ] == '"' ) $state = 'D';

                    else if ( $sql[ $current ] == '[' ) $state = 'F';

                    else if ( $sql[ $current ] == ' ' ) $state = 'E';


                    $string.=$sql[ $current ];
                    break;

                case 'B':
                    if ( $sql[ $current ] == '`' ) $state = 'A';

                    $string.=$sql[ $current ];
                    break;

                case 'C':
                    if ( $sql[ $current ] == '\'' ) $state = 'A';

                    $string.=$sql[ $current ];
                    break;

                case 'D':
                    if ( $sql[ $current ] == '"' ) $state = 'A';

                    $string.=$sql[ $current ];
                    break;

                case 'F':
                    if ( $sql[ $current ] == '[' ) $state = 'A';

                    $string.=$sql[ $current ];
                    break;

                case 'E':

                    if ( $sql[ $current ] != ' ' ) {

                        $string.=$sql[ $current ];

                        if ( $sql[ $current ] == '`' ) $state = 'B';

                        else if ( $sql[ $current ] == '\'' ) $state = 'C';

                        else if ( $sql[ $current ] == '"' ) $state = 'D';

                        else if ( $sql[ $current ] == '[' ) $state = 'F';
                        else $state = 'A';
                    }

                    break;
            }

            $current++;
        }

        return trim( $string );
    }

}
